﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MSSQL_Connection;
using IPL;
using SHARED;
using XML;
using System.Xml.Linq;

namespace GUI_Test
{
    public partial class formMain : Form
    {
        // List<SHARED.IDb> dbs = new List<SHARED.IDb>();
        SHARED.IDb db = null;

        Dictionary<string, SHARED.IDb> dicDbs = new Dictionary<string, IDb>();
        int dbCount = 1;
        public formMain()
        {
            InitializeComponent();
            //textBoxSQL.Text = "SELECT WC_SCHOOL.* FROM WC_SCHOOL LIMIT 10";
        }


        private void Convert(object sender, EventArgs e)
        {
            try
            {
                if (BwFileGeneration.IsBusy != true)
                {
                    textBoxXML.Text = string.Empty;

                    if (validateUserInput() != string.Empty)
                    {
                        MessageBox.Show(validateUserInput(), "Error: Invalid entry");
                        return;
                    }
                    if (dicDbs.Count == 0)
                    {
                        MessageBox.Show("Please connect to a database.", "Error: Invalid entry");
                        return;
                    }

                    Lexer lexer = new Lexer(textBoxSQL.Text.Trim(), (IDb)cmbServerName.SelectedValue);
                    Parser parser = new Parser(lexer);
                    ParserOutput pOutput = parser.Parse();

                    BuildTree buildtree = new BuildTree();

                    // MasterElementList list = buildtree.BuildTreeFromDatabase(db, pOutput.SQL, pOutput.ElementList, pOutput.RootElement);

                    string schemaText = buildtree.GetSchema(pOutput.ElementList);
                    if (schemaText == string.Empty)
                    {
                        //could not generate schema
                        MessageBox.Show("Cannot generate the XML schema", "Error");
                        return;
                    }
                    textBoxXML.Text = 
                        "##### CONVERTED SQL QUERY\r\n\r\n"
                        + pOutput.SQL + "\r\n\r\n\r\n"
                        + "##### XML OUTPUT FORMAT #####\r\n\r\n"
                        + schemaText;

                    //MessageBox.Show("schema generated");
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show("Unknown error: " + ex.Message, "Exception");
                return;
            }

        }

        private string validateUserInput()
        {

            string errorMessage = string.Empty;

            if (textBoxSQL.Text.Trim() == string.Empty)
            {
                errorMessage += "Please enter your SQL statement.";
                return errorMessage;
            }
            if (textBoxSQL.Text.ToLower().Contains("SELECT".ToLower()) == false)
            {
                errorMessage += "The SQL statement is missing keyword SELECT or has an invalid terminator.";
                return errorMessage;
            }
            //if (textBoxSQL.Text.Contains(";") == false)
            //{
            //    errorMessage += "The SQL statement is missing keyword SELECT or has an invalid terminator.";
            //    return errorMessage;
            //}
            if (textBoxSQL.Text.ToLower().Contains("FROM".ToLower()) == false)
            {
                errorMessage += "The SQL statement is missing keyword SELECT has an invalid terminator.";
                return errorMessage;
            }
            return errorMessage;
        }


        private void menuQuit_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void cutToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            if (textBoxSQL.SelectedText != "")
            {
                textBoxSQL.Cut();
            }
        }

        private void menuCopy_Click(object sender, EventArgs e)
        {
            if (textBoxSQL.SelectedText != "")
            {
                textBoxSQL.Copy();
            }
        }

        private void menuPaste_Click(object sender, EventArgs e)
        {
            textBoxSQL.Paste();
        }

        private void menuUndo_Click(object sender, EventArgs e)
        {
            if (textBoxSQL.CanUndo == true)
            {
                textBoxSQL.Undo();
            }
        }


        private void menuOracle_Click(object sender, EventArgs e)
        {
            //SubFormOracleConnection formChild;
            using (SubFormOracleConnection formChild = new SubFormOracleConnection())
            {
                int portNumber;
                while (formChild.textBoxOracleServerName.Text.Trim() == string.Empty || formChild.textBoxOracleDBName.Text.Trim() == string.Empty ||
                        formChild.textBoxOracleServiceId.Text.Trim() == string.Empty || formChild.textBoxOracleUserId.Text.Trim() == string.Empty ||
                        formChild.textBoxOraclePassword.Text == string.Empty || int.TryParse(formChild.textBoxOracleDBName.Text, out portNumber) == false)
                {
                    System.Windows.Forms.DialogResult res = formChild.ShowDialog();
                    if (res == System.Windows.Forms.DialogResult.OK)
                    {
                        if (formChild.textBoxOracleServerName.Text.Trim() != string.Empty && formChild.textBoxOracleDBName.Text.Trim() != string.Empty &&
                            formChild.textBoxOracleServiceId.Text.Trim() != string.Empty && formChild.textBoxOracleUserId.Text.Trim() != string.Empty &&
                            formChild.textBoxOraclePassword.Text != string.Empty && int.TryParse(formChild.textBoxOracleDBName.Text, out portNumber) == true)
                        {

                            db = new SHARED.Oracle(formChild.textBoxOracleServerName.Text, int.Parse(formChild.textBoxOracleDBName.Text),
                                 formChild.textBoxOracleServiceId.Text, formChild.textBoxOracleUserId.Text, formChild.textBoxOraclePassword.Text);

                            dicDbs.Add(dbCount + ".ORACLE: " + formChild.textBoxOracleServerName.Text, db);
                            dbCount++;
                            HandleComboBox();

                            formChild.Dispose();
                      
                        }
                    }
                    if (res == System.Windows.Forms.DialogResult.Cancel)
                    {
                        formChild.Dispose();
                        break;
                    }
                }
            }


        }

        private void btn_CreateXml_Click(object sender, EventArgs e)
        {
            try
            {
                if (BwFileGeneration.IsBusy != true)
                {
                    if (validateUserInput() != string.Empty)
                    {
                        MessageBox.Show(validateUserInput(), "Error: Invalid entry");
                        return;
                    }
                    if (!useDTDToolStripMenuItem.Checked && !menuXSD.Checked)
                    {
                        MessageBox.Show("Please select type of xml file to be generated.", "Error: Invalid entry");
                        return;
                    }
                    if (dicDbs.Count == 0)
                    {
                        MessageBox.Show("Please connect to a database.", "Error: Invalid entry");
                        return;
                    }
                    //if (db == null)
                    //{
                    //    MessageBox.Show("Please connect to a database.", "Error");
                    //    return;
                    //}

                    Lexer lexer = new Lexer(textBoxSQL.Text.Trim(), (IDb)cmbServerName.SelectedValue);
                    Parser parser = new Parser(lexer);
                    ParserOutput pOutput = parser.Parse();

                    //BuildTree buildtree = new BuildTree();

                    int MaxLines = ((IDb)cmbServerName.SelectedValue).GetNumberOfRowsAffected(pOutput.SQL);
                    if (MaxLines == 0)
                    {
                        MessageBox.Show("Zero number of records retrieved.", "Error");
                        return;
                    }


                    List<object> obj = new List<object>();
                    obj.Add(pOutput);
                    obj.Add((IDb)cmbServerName.SelectedValue);

                    pbTree.Visible = true;
                    pbTree.Maximum = MaxLines;
                    pbTree.Step = 1;
                    pbTree.Value = 1;

                    BwFileGeneration.RunWorkerAsync(obj);
                }

                //MasterElementList list = buildtree.BuildTreeFromDatabase((IDb)cmbServerName.SelectedValue, pOutput.SQL, pOutput.ElementList, pOutput.RootElement);

                //XElement result = null;
                //if (pOutput.SearchType == 2)
                //{
                //    result = buildtree.GenerateXMLUsingCompressionTechnique(list);
                //}
                //else if (pOutput.SearchType == 1)
                //{
                //    result = buildtree.GenerateXMLUsingGroupingTechnique(list);
                //}
                //else
                //{
                //    result = buildtree.GenerateBasicXML(list);
                //}

                //Output o = new Output("output");

                ////generate either xsd or dtd based on user selection
                //if (useDTDToolStripMenuItem.Checked)
                //{
                //    o.toDTD(pOutput.ElementList, result);
                //}
                //else
                //{
                //    o.toXSD(result);
                //}

                //MessageBox.Show("file is successfully created.");
            }
            catch (Exception ex)
            {
                pbTree.Visible = false;
                pbTree.Value = 1;
                MessageBox.Show("Unknown error: " + ex.Message, "Exception");
                return;
            }


        }

        //displau MSSQL form
        private void menuMSSQL_Click(object sender, EventArgs e)
        {


            using (SubFormMSSQLConnection formChilid = new SubFormMSSQLConnection())
            {
                while (formChilid.textBoxMSSQLServerName.Text.Trim() == string.Empty || formChilid.textBoxMSSQLDBName.Text.Trim() == string.Empty ||
                    formChilid.textBoxMSSQLUserId.Text.Trim() == string.Empty || formChilid.textBoxMSSQLPassword.Text.Trim() == string.Empty)
                {
                    System.Windows.Forms.DialogResult res = formChilid.ShowDialog();
                    if (res == System.Windows.Forms.DialogResult.Cancel)
                    {
                        formChilid.Dispose();
                        break;
                    }
                    if (res == System.Windows.Forms.DialogResult.OK)
                    {
                        if (formChilid.textBoxMSSQLServerName.Text.Trim() != string.Empty && formChilid.textBoxMSSQLDBName.Text.Trim() != string.Empty &&
                                formChilid.textBoxMSSQLUserId.Text.Trim() != string.Empty && formChilid.textBoxMSSQLPassword.Text.Trim() != string.Empty)
                        {
                            db = new SHARED.MSSQL(formChilid.textBoxMSSQLServerName.Text, formChilid.textBoxMSSQLDBName.Text,
                                formChilid.textBoxMSSQLUserId.Text, formChilid.textBoxMSSQLPassword.Text);

                            dicDbs.Add(dbCount + ".MSSQL: " + formChilid.textBoxMSSQLServerName.Text, db);
                            dbCount++;

                            HandleComboBox();


                            formChilid.Dispose();
                        }
                    }

                }
            }


        }
        //display MYSQL form
        private void menuMYSQL_Click(object sender, EventArgs e)
        {
            //SubFormMYSQLConnection formChild;
            using (SubFormMYSQLConnection formChild = new SubFormMYSQLConnection())
            {
                //                while (formChild.textBoxMYSQLServerName.Text.Trim() == string.Empty || formChild.textBoxMYSQLDBName.Text.Trim() == string.Empty || formChild.textBoxMYSQLUserId.Text == string.Empty)
                //{
                System.Windows.Forms.DialogResult res = formChild.ShowDialog();
                if (res == System.Windows.Forms.DialogResult.OK)
                {
                    if (formChild.textBoxMYSQLServerName.Text.Trim() != string.Empty && formChild.textBoxMYSQLDBName.Text.Trim() != string.Empty && formChild.textBoxMYSQLUserId.Text != string.Empty)
                    {
                        /*****************************************************/
                        db = new SHARED.MYSQL(formChild.textBoxMYSQLServerName.Text, formChild.textBoxMYSQLDBName.Text,
                            formChild.textBoxMYSQLUserId.Text, formChild.textBoxMYSQLPassword.Text);

                        dicDbs.Add(dbCount + ".MYSQL: " + formChild.textBoxMYSQLServerName.Text, db);
                        dbCount++;
                        HandleComboBox();

                        formChild.Dispose();
                    }
                }
                if (res == System.Windows.Forms.DialogResult.Cancel)
                {
                    formChild.Dispose();
                    //break;
                }
                //}
            }
        }
        //display Access form
        private void menuAccess_Click(object sender, EventArgs e)
        {
            //SubFormAccessConnection formChild;
            using (SubFormAccessConnection formChild = new SubFormAccessConnection())
            {
                while (formChild.textBoxAccessFilename.Text.Trim() == string.Empty)
                {
                    System.Windows.Forms.DialogResult res = formChild.ShowDialog();
                    if (res == System.Windows.Forms.DialogResult.OK)
                    {
                        if (formChild.textBoxAccessFilename.Text.Trim() != string.Empty)
                        {
                            db = new SHARED.Access(formChild.textBoxAccessFilename.Text, formChild.textBoxAccessPassword.Text);
                            dicDbs.Add(dbCount + ".ACCESS: " + formChild.textBoxAccessFilename.Text, db);
                            dbCount++;
                            HandleComboBox();

                            formChild.Dispose();
                        }
                    }
                    if (res == System.Windows.Forms.DialogResult.Cancel)
                    {
                        formChild.Dispose();
                        break;
                    }

                }
            }
        }

        private void textboxSQL_TextChanged(object sender, EventArgs e)
        {
            //textBoxSQL.CharacterCasing = CharacterCasing.Upper;
        }

        private void HandleComboBox()
        {
            cmbServerName.DataSource = new BindingSource(dicDbs, null);
            cmbServerName.DisplayMember = "key";
            cmbServerName.ValueMember = "Value";

        }

        private void menuXSD_Click(object sender, EventArgs e)
        {
            if (useDTDToolStripMenuItem.Checked)
            {
                useDTDToolStripMenuItem.Checked = false;
            }

        }

        private void useDTDToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (menuXSD.Checked)
            {
                menuXSD.Checked = false;
            }
        }

        private void cmbServerName_TextChanged(object sender, EventArgs e)
        {
            if (cmbServerName.Items.Count == 1)
            {
                cmbServerName.SelectedIndex = 0;
            }
            if (cmbServerName.Items.Count > 1)
            {
                cmbServerName.Enabled = true;
            }
        }

        private void BwFileGeneration_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                BuildTree buildtree = new BuildTree();
                List<object> args = (List<object>)e.Argument;
                ParserOutput p = (ParserOutput)args[0];



                MasterElementList list = buildtree.BuildTreeFromDatabase((IDb)args[1], p.SQL, p.ElementList, p.RootElement, BwFileGeneration);

                XElement result = null;
                if (p.SearchType == 2)
                {
                    result = buildtree.GenerateXMLUsingCompressionTechnique(list);
                }
                else if (p.SearchType == 1)
                {
                    result = buildtree.GenerateXMLUsingGroupingTechnique(list);
                }
                else
                {
                    result = buildtree.GenerateBasicXML(list);
                }

                Output o = new Output("output");

                //generate either xsd or dtd based on user selection
                if (useDTDToolStripMenuItem.Checked)
                {
                    o.toDTD(p.ElementList, result);
                }
                else
                {
                    o.toXSD(result);
                }
            }
            catch
            {
                throw;
            }
        }

        private void BwFileGeneration_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            pbTree.Value = e.ProgressPercentage;
        }

        private void BwFileGeneration_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if ((e.Cancelled == true))
            {
                this.pbTree.Visible = false;
                this.pbTree.Value = 1;
            }

            else if (!(e.Error == null))
            {
                this.pbTree.Visible = false;
                this.pbTree.Value = 1;
                throw e.Error;
            }

            else
            {

                MessageBox.Show("File is successfully created at this location: C:\\temp", "Result");
                this.pbTree.Visible = false;
                this.pbTree.Value = 1;
            }

        }

        private void selectAllToolStripMenuItem_Click(object sender, EventArgs e)
        {

              textBoxSQL.SelectAll();

        }


    }
}
